package com.ccxc.question.exam.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.ccxc.question.exam.dto.AreaDTO2;
import com.ccxc.question.exam.dto.DailyDTO;
import com.ccxc.question.exam.dto.ErrorDTO;
import com.ccxc.question.exam.dto.RankingDTO;
import com.ccxc.question.exam.dto.SummaryDTO;

@Component
public class InformationDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * @DEscription 功能说明 建立答题汇总表
     * 参数说明：
     */
    public SummaryDTO build2(String userid) {
        final String sql = "INSERT INTO  t_summary_statistics (`USER_ID`,CUMULATIVE_TOTAL_SCORE, CUMULATIVE_CORRECT_NUMBER, CUMULATIVE_ERROR_NUMBER,CUMULATIVE_TOTAL_NUMBER) VALUES (?, 0, 0, 0, 0)";
        SummaryDTO summaryDto = new SummaryDTO();
        summaryDto.setUserid(userid);
        jdbcTemplate.update(sql, summaryDto.getUserid());
        return summaryDto;

    }

    /**
     * @DEscription 功能说明 查询每日答题记录是否为空
     * 参数说明：
     */
    public boolean findByuserId3(String id) {
        final String sql = "SELECT CORRECT_NUMBER,ERROR_NUMBER,TOTAL_NUMBER,CURRENT_SCORE,THE_DAY_SCORE,TIME  FROM t_daily_statistics  WHERE USER_ID = ? ORDER BY `TIME` DESC ";
        List<DailyDTO> list = jdbcTemplate.query(sql, new Object[]{id}, new RowMapper<DailyDTO>() {
            public DailyDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                DailyDTO dto = new DailyDTO();
                dto.setDaycorrect(rs.getInt("CORRECT_NUMBER"));
                dto.setDayerror(rs.getInt("ERROR_NUMBER"));
                dto.setDaynumber(rs.getInt("TOTAL_NUMBER"));
                dto.setScore(rs.getInt("CURRENT_SCORE"));
                dto.setDayscore(rs.getInt("THE_DAY_SCORE"));
                dto.setTime(rs.getTimestamp("TIME"));
                dto.setUserid(id);
                dto.getUserid();
                return dto;
            }
        });
        if (null != list && list.size() > 0) {
            return true;
        }
        return false;
    }

    /**
     * @DEscription 功能说明 查询每日答题记录，并且返回列表形式
     * 参数说明：
     */
    public List<DailyDTO> findByuserId(String id) {
        final String sql = "SELECT CORRECT_NUMBER,ERROR_NUMBER,TOTAL_NUMBER,CURRENT_SCORE,THE_DAY_SCORE,TIME  FROM t_daily_statistics  WHERE USER_ID = ? ORDER BY `TIME` DESC ";
        List<DailyDTO> list = jdbcTemplate.query(sql, new Object[]{id}, new RowMapper<DailyDTO>() {
            public DailyDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                DailyDTO dto = new DailyDTO();
                dto.setDaycorrect(rs.getInt("CORRECT_NUMBER"));
                dto.setDayerror(rs.getInt("ERROR_NUMBER"));
                dto.setDaynumber(rs.getInt("TOTAL_NUMBER"));
                dto.setScore(rs.getInt("CURRENT_SCORE"));
                dto.setDayscore(rs.getInt("THE_DAY_SCORE"));
                dto.setTime(rs.getTimestamp("TIME"));
                dto.setUserid(id);
                dto.getUserid();
                return dto;
            }
        });
        if (null != list && list.size() > 0) {
            return list;
        }
        return null;
    }

    /**
     * @DEscription 功能说明  查询答题汇总表信息
     * 参数说明：
     */
    public SummaryDTO findByuserId2(String id) {
        final String sql = "SELECT CUMULATIVE_TOTAL_SCORE,CUMULATIVE_CORRECT_NUMBER,CUMULATIVE_ERROR_NUMBER,CUMULATIVE_TOTAL_NUMBER  FROM t_summary_statistics WHERE USER_ID = ?;";
        List<SummaryDTO> list = jdbcTemplate.query(sql, new Object[]{id}, new RowMapper<SummaryDTO>() {
            public SummaryDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                SummaryDTO dto = new SummaryDTO();
                dto.setCorrect(rs.getInt("CUMULATIVE_CORRECT_NUMBER"));
                dto.setError(rs.getInt("CUMULATIVE_ERROR_NUMBER"));
                dto.setTotal_number(rs.getInt("CUMULATIVE_TOTAL_NUMBER"));
                dto.setScore(rs.getInt("CUMULATIVE_TOTAL_SCORE"));
                dto.setUserid(id);
                dto.getUserid();
                return dto;
            }
        });
        if (null != list && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    /**
     * @DEscription 功能说明 查询答题记录表返回列表形式
     * 参数说明：
     */
    public List<RankingDTO> ranking() {
        final String sql = "SELECT CUMULATIVE_TOTAL_SCORE,USER_ID,u.HEAD_PORTRAIT,u.COUNTRY,u.NICKNAME FROM t_summary_statistics AS s INNER JOIN t_user_information AS u ON u.ID = s.USER_ID  ORDER BY CUMULATIVE_TOTAL_SCORE DESC  LIMIT 0,50;";
        List<RankingDTO> list = jdbcTemplate.query(sql, new Object[]{}, new RowMapper<RankingDTO>() {
            public RankingDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                RankingDTO dto = new RankingDTO();
                dto.setCountry(rs.getString("u.COUNTRY"));
                dto.setNickname(rs.getString("u.NICKNAME"));
                dto.setScore(rs.getString("CUMULATIVE_TOTAL_SCORE"));
                dto.setTouxiang(rs.getString("u.HEAD_PORTRAIT"));
                dto.setUserid(rs.getString("USER_ID"));
                return dto;
            }
        });
        if (null != list && list.size() > 0) {
            return list;
        }
        return null;
    }

    /**
     * @DEscription 功能说明 查询题目的知识领域
     * 参数说明：
     */
    public List<AreaDTO2> area(String id) {
        final String sql = "SELECT COUNT(1) totalNum,THE_KNOWLEDGE_AREA FROM t_record WHERE USER_ID = ? AND JUDGE = 1 GROUP BY THE_KNOWLEDGE_AREA  ORDER BY totalNum desc LIMIT 3";
        return jdbcTemplate.query(sql,
                new Object[]{id},
                new int[]{Types.VARCHAR},
                (ResultSet rs, int rowNum) -> new AreaDTO2(rs.getString("THE_KNOWLEDGE_AREA"))
        );

    }

    /**
     * @DEscription 功能说明 查询错误题目
     * 参数说明：
     */
    public List<ErrorDTO> error(String id) {
        final String sql = "SELECT\r\n" +
                "	b.cs,\r\n" +
                "	b.QUESTION_ID,\r\n" +
                "	b.SUBMIT_ANSWER,\r\n" +
                "	q.CONTENT,\r\n" +
                "	q.KNOWLEDGE_AREA,\r\n" +
                "	q.ANSWER\r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		SELECT\r\n" +
                "			SUBMIT_ANSWER,\r\n" +
                "			QUESTION_ID,\r\n" +
                "			USER_ID,\r\n" +
                "			COUNT(*) AS cs\r\n" +
                "		FROM\r\n" +
                "			t_record\r\n" +
                "		GROUP BY\r\n" +
                "			QUESTION_ID\r\n" +
                "	) AS b\r\n" +
                "INNER JOIN t_question AS q ON q.ID = b.QUESTION_ID\r\n" +
                "WHERE\r\n" +
                "	b.USER_ID = ?";
        return jdbcTemplate.query(sql,
                new Object[]{id},
                new int[]{Types.VARCHAR},
                (ResultSet rs, int rowNum) -> new ErrorDTO(rs.getString("b.cs"), rs.getString("b.QUESTION_ID"), rs.getString("q.CONTENT"), rs.getString("b.SUBMIT_ANSWER"), rs.getString("q.KNOWLEDGE_AREA"), rs.getString("q.ANSWER"))
        );
    }

    /**
     * @DEscription 功能说明  查询错题id
     * 参数说明：
     */
    public List<ErrorDTO> error2(String id) {
        final String sql = "SELECT QUESTION_ID FROM t_record WHERE JUDGE = 0 AND USER_ID = ?";
        List<ErrorDTO> list = jdbcTemplate.query(sql, new Object[]{id}, new RowMapper<ErrorDTO>() {
            public ErrorDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                ErrorDTO dto = new ErrorDTO();
                dto.setQueid(rs.getString("QUESTION_ID"));
                return dto;
            }
        });
        if (null != list && list.size() > 0) {
            return list;
        }
        return null;
    }

    /**
     * @DEscription 功能说明 查询用户自己的排名
     * 参数说明：
     */
    public int userranking(String id) {
        final String sql = "SELECT b.rownum  FROM(SELECT t.*,@rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r,t_summary_statistics AS t ORDER BY t.CUMULATIVE_TOTAL_SCORE DESC	) AS b WHERE	b.USER_ID = ?";
        List<SummaryDTO> list = jdbcTemplate.query(sql, new Object[]{id}, new RowMapper<SummaryDTO>() {
            public SummaryDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                SummaryDTO summaryDTO = new SummaryDTO();
                summaryDTO.setRanking(rs.getInt("b.rownum"));

                return summaryDTO;
            }
        });

        if (null != list && list.size() > 0) {
            return list.get(0).getRanking();
        }
        return 0;
    }
}
